﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using QDT.Common;
using QDT.Core;
using QDT.Core.Deals;
using QDT.Core.Projects;
using QDT.Services.DTO;

namespace QDT.Services.Deals
{
    public class QureyService : ServiceBase, IQureyService
    {
        /// <summary>
        /// 查询投资列表
        /// </summary>
        /// <param name="userId">用户ID</param>
        /// <param name="pageIndex">页索引</param>
        /// <param name="pageSize">页尺寸</param>
        /// <param name="status">项目状态</param>
        /// <returns></returns>
        public IPagedList<Credit> GetCreditList(int userId, int pageIndex, int pageSize, ProjectStatus status)
        {
            var p1 = pageIndex * pageSize;
            var p2 = p1 + pageSize;

            var sqlCount = "select count(credit.ProjectID) from qdt_credit as credit,qdt_project as deal where credit.UserID='{0}' and credit.ProjectID=deal.id and deal.Status='{1}';".FormatWith(userId, (int)status);

            var sql = @"SELECT * FROM
                                (SELECT ROW_NUMBER() OVER(ORDER BY credit.id DESC) AS rowNum, credit.*, credit.Type as InnerType, credit.Status as InnerStatus FROM 
                                    qdt_credit AS credit, qdt_project AS deal
                                        WHERE credit.UserID='{0}' AND credit.Money>0 AND credit.ProjectID=deal.id AND deal.Status='{1}') as results
                                            WHERE results.rowNum>'{2}'AND results.rowNum<='{3}';".FormatWith(userId, (int)status, p1, p2);

            var count = DbProvider.QuerySql<int>(sqlCount).First();
            var result = DbProvider.QuerySql<Credit>(sql);
            return new StaticPagedList<Credit>(result, pageIndex, pageSize, count);
        }

        public decimal GetUserRepayMoney(DateTime dateTime)
        {
            var sql = @"select COALESCE(SUM(log.money), 0) from qdt_user_log as log,qdt_user as us 
                                where us.id=log.user_id And us.group_id='3' And 
                                    log.log_time>='{0}' And log.log_time<'{1}' And log.Trade IN ('8', '9');".FormatWith(dateTime.Date, dateTime.AddDays(1).Date);
            var result = DbProvider.QuerySql<decimal>(sql).First();
            return result;
        }

        public ICollection<UserDTO> GetNewInvester(DateTime date)
        {
            var start = date.Date;
            var end = start.AddDays(1);
            var sql =
                @"SELECT UserID,UserName FROM (SELECT DISTINCT(c.UserID),c.UserName FROM qdt_credit AS c, qdt_user AS u WHERE c.CreateTime>='{0}' AND c.CreateTime<'{1}' AND u.id=c.UserID AND u.group_id='3') as c 
	                    WHERE c.UserID NOT IN 
	                        (SELECT DISTINCT(cc.UserID) FROM qdt_credit as cc 
			                        WHERE cc.UserID IN (c.UserID) AND cc.CreateTime<'{0}');".FormatWith(start, end);
            var result = DbProvider.QuerySql<UserDTO>(sql).ToList();
            return result;
        }

        public int GetInvesterCount()
        {
            const string sql = "SELECT COUNT(DISTINCT(c.UserID)) FROM qdt_credit AS c, qdt_user AS u WHERE c.ProjectID>'182' AND u.id=c.UserID AND u.group_id='3';";
            var result = DbProvider.QuerySql<int>(sql).First();
            return result;
        }
    }
}
